package com.users.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.users.bean.Users;
import com.users.dao.UsersDao;
import com.users.util.PaginationSupport;

public class UsersDaoImpl extends JdbcDaoSupport implements UsersDao{
	/**
	 * 通过用户名和密码获取用户信息
	 * @param userName 用户名
	 * @param passWord 用户密码
	 * @return 用户信息
	 */
	@SuppressWarnings("unchecked")
	public Users getUsersByUserNameAndPassWord(String userName, String passWord) {
		// TODO 查询用户信息
		String selSql = "select * from users u where u.userName=? and u.passWord=?";
		List<Users> list = super.getJdbcTemplate().query(selSql, new Object[]{userName, passWord}, new RowMapper(){
			public Users mapRow(ResultSet rs, int i) throws SQLException {
				Users users = new Users();
				users.setuId(rs.getInt("uId"));
				users.setUserName(rs.getString("userName"));
				users.setPassWord(rs.getString("passWord"));
				return users;
			}
		});
		return ((list != null && list.size() > 0) ? list.get(0) : null);
	}

	/**
	 * 新增用户
	 * @param users
	 * @return
	 */
	public int addUsers(Users users) {
		// TODO 新增用户信息
		String insSql = "insert into users(userName,passWord) values(?,?)";
		return super.getJdbcTemplate().update(insSql, new Object[]{users.getUserName(), users.getPassWord()});
	}

	/**
	 * 删除用户
	 * @param users
	 * @return
	 */
	public int delUsers(Users users) {
		// TODO 删除用户信息
		String delSql = "delete from users where uid=? and userName=?";
		return super.getJdbcTemplate().update(delSql, new Object[]{users.getuId(), users.getPassWord()});
	}

	/**
	 * 分页查询所有用户
	 * @param pageSize 每页条数
	 * @param startIndex 开始页数
	 * @param keyValue
	 * @param users
	 * @return
	 */
	@SuppressWarnings("unchecked")
    public PaginationSupport queryUsersByPage(final int pageSize, final int startIndex, final Users users) {
		String sql = "SELECT COUNT(UID) FROM USERS where 1=1";
		if (users.getUserName() != null && !"".equals(users.getUserName())) {
			sql += " and userName like '" + users.getUserName() + "%'";
		}
		int count = getJdbcTemplate().queryForInt(sql);
		StringBuilder selSql = new StringBuilder("SELECT * FROM USERS WHERE 1=1 ");
		if (users.getUserName() != null && !"".equals(users.getUserName())) {
			selSql.append(" and userName like '" + users.getUserName() + "%'");
		}
		selSql.append(" AND UID >= (SELECT UID FROM USERS ORDER BY UID LIMIT ").append(startIndex).append(",1)");
		selSql.append(" LIMIT ").append(pageSize);
		List<Users> lists = getJdbcTemplate().query(selSql.toString(), cityMapper);
		System.out.println("分页SQL语句为：" + selSql.toString());
		PaginationSupport paginationSupport = new PaginationSupport(lists, count, pageSize, startIndex);
		return paginationSupport;
	}
	/**
	 * 
	 * @param UserName
	 * @return
	 */
	public List<String> queryUserName(String UserName) {
		String selSql = "select userName from users where 1=1";
		if (UserName != null && !"".equals(UserName)) {
			selSql += " userName like '%" + UserName + "%'";
		}
		List<String> list = new ArrayList<String>();
		return list = getJdbcTemplate().query(selSql, new RowMapper(){
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString("userName");
			}
		});
	}

	private static final RowMapper cityMapper = new RowMapper(){
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			Users users = new Users();
			users.setuId(rs.getInt("uid"));
			users.setUserName(rs.getString("userName"));
			users.setPassWord(rs.getString("passWord"));
			return users;
		}
	};

	/**
	 * 查询所有用户
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Users> queryUserList() {
		List<Users> list = new ArrayList<Users>();
		String selSql = "SELECT * FROM USERS WHERE 1=1";
		list = getJdbcTemplate().query(selSql, cityMapper);
		return list;
	}
}
